from pyecharts import options as opts
from pyecharts.charts import Pie, Bar, Line, Boxplot
from db_utils import DBUtils

def create_category_pie_chart():
    """
    创建书籍类别分布占比饼图
    """
    # 创建数据库连接
    db = DBUtils(use_env=False)

    try:
        # 查询各个类别的书籍数量
        sql = """
            SELECT category, COUNT(*) as count 
            FROM book_info 
            GROUP BY category 
            ORDER BY count DESC
        """

        # 执行查询
        result = db.execute_query(sql)
    finally:
        # 关闭数据库连接
        db.close_connect()

    # 准备数据
    categories = []
    counts = []
    for row in result:
        categories.append(row['category'])
        counts.append(row['count'])

    # 创建饼图
    pie = (
        Pie()
        .add(
            series_name="书籍数量",
            data_pair=list(zip(categories, counts)),
            radius=["40%", "70%"],  # 设置内外半径，形成环形图
            label_opts=opts.LabelOpts(
                position="outside",
                formatter="{b}: {c} ({d}%)"  # 显示类别、数量和百分比
            )
        )
        .set_global_opts(
            title_opts=opts.TitleOpts(
                title="书籍类别分布占比",
                subtitle="各类别书籍数量统计",
                pos_left="center"
            ),
            legend_opts=opts.LegendOpts(
                orient="vertical",
                pos_left="left"
            )
        )
    )

    return pie

def create_category_clicks_bar():
    """
    创建不同类别书籍的平均总点击量对比柱状图
    """
    # 创建数据库连接
    db = DBUtils(use_env=False)

    try:
        # 查询各个类别的平均点击量
        sql = """
            SELECT 
                category,
                ROUND(AVG(total_clicks), 2) as avg_clicks,
                COUNT(*) as book_count
            FROM book_info 
            GROUP BY category 
            ORDER BY avg_clicks DESC
        """

        # 执行查询
        result = db.execute_query(sql)
    finally:
        # 关闭数据库连接
        db.close_connect()

    # 准备数据
    categories = []
    avg_clicks = []
    book_counts = []
    for row in result:
        categories.append(row['category'])
        avg_clicks.append(row['avg_clicks'])
        book_counts.append(row['book_count'])

    # 创建柱状图
    bar = (
        Bar()
        .add_xaxis([f"{cat}({count})" for cat, count in zip(categories, book_counts)])
        .add_yaxis(
            series_name="平均点击量",
            y_axis=avg_clicks,
            label_opts=opts.LabelOpts(
                position="top",
                formatter="{c}"
            )
        )
        .set_global_opts(
            title_opts=opts.TitleOpts(
                title="不同类别书籍的平均总点击量对比",
                subtitle="括号中显示每个类别的样本数量",
                pos_left="center"
            ),
            legend_opts=opts.LegendOpts(is_show=False),
            xaxis_opts=opts.AxisOpts(
                name="类别（样本数）",
                axislabel_opts=opts.LabelOpts(rotate=45)
            ),
            yaxis_opts=opts.AxisOpts(
                name="平均点击量",
                name_location="middle",
                name_gap=50
            ),
            tooltip_opts=opts.TooltipOpts(
                trigger="axis",
                axis_pointer_type="shadow"
            ),
            datazoom_opts=[
                opts.DataZoomOpts(
                    type_="slider",
                    orient="horizontal",
                    range_start=0,
                    range_end=100
                ),
                opts.DataZoomOpts(
                    type_="inside",
                    range_start=0,
                    range_end=100
                )
            ]
        )
    )

    return bar

def create_books_ranking_bar():
    """
    创建书籍点击量排名的条形图
    """
    # 创建数据库连接
    db = DBUtils(use_env=False)

    try:
        # 查询书籍点击量排名前20
        sql = """
            SELECT 
                book_name,
                total_clicks
            FROM book_info 
            ORDER BY total_clicks DESC
            LIMIT 20
        """

        # 执行查询
        result = db.execute_query(sql)
    finally:
        # 关闭数据库连接
        db.close_connect()

    # 准备数据
    book_names = []
    clicks = []
    for row in result:
        book_names.append(row['book_name'])
        clicks.append(row['total_clicks'])

    # 创建条形图
    bar = (
        Bar()
        .add_xaxis(book_names)
        .add_yaxis(
            series_name="点击量",
            y_axis=clicks,
            label_opts=opts.LabelOpts(
                position="right",
                formatter="{c}"
            )
        )
        .reversal_axis()  # 使条形图横向显示
        .set_global_opts(
            title_opts=opts.TitleOpts(
                title="书籍点击量排名TOP20",
                pos_left="center"
            ),
            xaxis_opts=opts.AxisOpts(
                name="点击量",
                name_location="middle",
                name_gap=30
            ),
            yaxis_opts=opts.AxisOpts(
                axislabel_opts=opts.LabelOpts(interval=0)  # 显示所有Y轴标签
            ),
            datazoom_opts=[opts.DataZoomOpts(type_="inside")]
        )
    )

    return bar

def create_words_recommendations_line():
    """
    创建总推荐数与总字数关系趋势的折线图
    """
    # 创建数据库连接
    db = DBUtils(use_env=False)

    try:
        # 查询书籍的总推荐数和总字数
        sql = """
            SELECT 
                total_words,
                total_recommendations
            FROM book_info 
            ORDER BY total_words
        """

        # 执行查询
        result = db.execute_query(sql)
    finally:
        # 关闭数据库连接
        db.close_connect()

    # 准备数据
    words = []
    recommendations = []
    for row in result:
        words.append(row['total_words'])
        recommendations.append(row['total_recommendations'])

    # 创建折线图
    line = (
        Line()
        .add_xaxis(words)
        .add_yaxis(
            series_name="总推荐数",
            y_axis=recommendations,
            is_smooth=True,
            label_opts=opts.LabelOpts(is_show=False),
            linestyle_opts=opts.LineStyleOpts(width=3),
            symbol_size=8
        )
        .set_global_opts(
            title_opts=opts.TitleOpts(
                title="总推荐数与总字数的关系趋势", 
                pos_left="center"
            ),
            legend_opts=opts.LegendOpts(is_show=False),
            tooltip_opts=opts.TooltipOpts(
                trigger="axis",
                formatter="{a}: {b}字<br/>{c}推荐"
            ),
            xaxis_opts=opts.AxisOpts(
                name="总字数",
                name_location="middle",
                name_gap=30
            ),
            yaxis_opts=opts.AxisOpts(
                name="总推荐数",
                name_location="middle",
                name_gap=50
            ),
            datazoom_opts=[
                opts.DataZoomOpts(
                    type_="slider",
                    orient="horizontal",
                    range_start=0,
                    range_end=100
                ),
                opts.DataZoomOpts(
                    type_="inside",
                    range_start=0,
                    range_end=100
                )
            ]
        )
    )

    return line

def create_status_boxplot():
    """
    创建不同状态书籍的周推荐数分布箱线图
    """
    # 创建数据库连接
    db = DBUtils(use_env=False)

    try:
        # 查询不同状态书籍的周推荐数
        sql = """
            SELECT 
                status,
                weekly_recommendations
            FROM book_info
            ORDER BY status
        """

        # 执行查询
        result = db.execute_query(sql)
    finally:
        # 关闭数据库连接
        db.close_connect()

    # 准备数据
    status_data = {}
    for row in result:
        status = row['status']
        if status not in status_data:
            status_data[status] = []
        status_data[status].append(row['weekly_recommendations'])

    # 计算箱线图所需数据
    x_data = list(status_data.keys())
    y_data = []
    for status in x_data:
        values = status_data[status]
        # 计算最小值、Q1、中位数、Q3、最大值
        sorted_values = sorted(values)
        n = len(sorted_values)
        q1 = sorted_values[int(n * 0.25)]
        median = sorted_values[int(n * 0.5)]
        q3 = sorted_values[int(n * 0.75)]
        min_val = sorted_values[0]
        max_val = sorted_values[-1]
        y_data.append([min_val, q1, median, q3, max_val])

    # 创建箱线图
    boxplot = (
        Boxplot()
        .add_xaxis(x_data)
        .add_yaxis("周推荐数", y_data)
        .set_global_opts(
            title_opts=opts.TitleOpts(
                title="不同状态书籍的周推荐数分布",
                pos_left="center"
            ),
            legend_opts=opts.LegendOpts(is_show=False),
            tooltip_opts=opts.TooltipOpts(
                trigger="item",
                formatter="{a}: {b}<br/>最小值: {c[0]}<br/>Q1: {c[1]}<br/>中位数: {c[2]}<br/>Q3: {c[3]}<br/>最大值: {c[4]}"
            ),
            xaxis_opts=opts.AxisOpts(
                name="书籍状态",
                name_location="middle",
                name_gap=30
            ),
            yaxis_opts=opts.AxisOpts(
                name="周推荐数",
                name_location="middle",
                name_gap=50
            )
        )
    )

    return boxplot

def create_books_clicks_ranking():
    """
    创建各书籍的总点击量排名条形图
    """
    # 创建数据库连接
    db = DBUtils(use_env=False)

    try:
        # 查询书籍名称和总点击量，按点击量降序排列
        sql = """
            SELECT 
                book_name,
                total_clicks
            FROM book_info
            ORDER BY total_clicks DESC
            LIMIT 20  # 只显示前20名
        """

        # 执行查询
        result = db.execute_query(sql)
    finally:
        # 关闭数据库连接
        db.close_connect()

    # 准备数据
    book_names = []
    total_clicks = []
    for row in result:
        # 处理书名过长问题
        short_name = row['book_name'][:15] + '...' if len(row['book_name']) > 15 else row['book_name']
        book_names.append(short_name)
        total_clicks.append(row['total_clicks'])

    # 创建条形图
    bar = (
        Bar()
        .add_xaxis(book_names)
        .add_yaxis(
            series_name="总点击量",
            y_axis=total_clicks,
            label_opts=opts.LabelOpts(
                position="right",
                formatter="{c}",
                color="#333"
            ),
            itemstyle_opts=opts.ItemStyleOpts(
                color="#1890ff",
                border_color="#096dd9",
                border_width=1
            )
        )
        .reversal_axis()  # 反转轴，使条形水平显示
        .set_global_opts(
            title_opts=opts.TitleOpts(
                title="各书籍的总点击量排名",
                subtitle="前20名书籍", 
                pos_left="center",
                title_textstyle_opts=opts.TextStyleOpts(font_size=18),
                subtitle_textstyle_opts=opts.TextStyleOpts(font_size=14)
            ),
            legend_opts=opts.LegendOpts(is_show=False),
            tooltip_opts=opts.TooltipOpts(
                trigger="axis",
                axis_pointer_type="shadow",
                formatter="{b}: {c}"
            ),
            xaxis_opts=opts.AxisOpts(
                name="总点击量",
                name_location="middle",
                name_gap=30,
                axislabel_opts=opts.LabelOpts(color="#333"),
                axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color="#666"))
            ),
            yaxis_opts=opts.AxisOpts(
                name="书籍名称",
                name_location="middle",
                name_gap=50,
                axislabel_opts=opts.LabelOpts(
                    interval=0,  # 显示所有y轴标签
                    color="#333",
                    font_size=12
                ),
                axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color="#666"))
            ),
            datazoom_opts=[
                opts.DataZoomOpts(
                    type_="slider",
                    orient="vertical",
                    yaxis_index=0,
                    range_start=0,
                    range_end=100
                ),
                opts.DataZoomOpts(
                    type_="inside",
                    orient="vertical",
                    yaxis_index=0,
                    range_start=0,
                    range_end=100
                )
            ]
        )
        .set_series_opts(
            itemstyle_opts=opts.ItemStyleOpts(
                color="#1890ff",
                border_color="#096dd9",
                border_width=1
            )
        )
    )

    return bar